﻿Imports DTO
Imports System.Data.SqlClient
Imports System.Data

Public Class CSachDAO
    Public Function LayTenSach(ByVal masach As Integer) As String
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_KiemTraMaSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ms", masach)
            cm.Parameters.Add(para)
            Dim str As String = cm.ExecuteScalar()
            cn.Close()
            Return str
        End Using
    End Function

    Public Function LayThongTinSach(ByVal _ma As Integer) As CSachDTO
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLayThongTinSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", _ma)
            cm.Parameters.Add(para)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                If (reader.Read()) Then
                    Return New CSachDTO(reader)
                End If
                Return Nothing
            End Using
        End Using
    End Function

    ' Lấy tiền phạt của cuốn sách khi biết mã:
    Public Function LayTienPhatKhiBietMa(ByVal masach As Integer) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_LayTienPhatKhiBietMaSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@masach", masach)
            cm.Parameters.Add(para)
            Dim tienphat As Integer = cm.ExecuteScalar()
            cn.Close()
            Return tienphat
        End Using
    End Function

    Public Function LaySoLuongSachDaMuon(ByVal ma As Integer) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLaySoLuongSachDaMuonCuaDocGia", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@madocgia", ma)
            cm.Parameters.Add(para)
            Dim soluong As Integer = cm.ExecuteScalar()
            cn.Close()
            Return soluong
        End Using
    End Function

    Public Function LayDanhSachSach() As List(Of CSachDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLayDanhSachSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                Dim lsach As New List(Of CSachDTO)
                While (reader.Read())
                    lsach.Add(New CSachDTO(reader))
                End While
                Return lsach
            End Using
        End Using
    End Function


    Public Function CapNhatSach(ByVal sach As CSachDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspCapNhatSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As SqlParameter = New SqlParameter("@ma", sach.Ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@matinhtrang", sach.MaTinhTrang)
            cm.Parameters.Add(para)
            Dim n As Integer = cm.ExecuteNonQuery()
            cn.Close()
            Return n
        End Using
    End Function


    Public Function TraCuu(ByVal masach As Integer) As CSachDTO
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_TraCuuBietMaSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@Ma", masach)
            cm.Parameters.Add(para)
            Dim sachdto As CSachDTO
            Dim reader As IDataReader = cm.ExecuteReader()
            If (reader.Read()) Then
                sachdto = New CSachDTO(reader)
            Else
                sachdto = Nothing
            End If
            Return sachdto
        End Using
    End Function


    Public Function TraCuuKhongCoMaSach(ByVal sachcrt As CSachCrt) As List(Of CSachDTO)
        Using cn As SqlConnection = CDataProvider.Connect()

            Dim sKTTenDauSach As String = " 1=1 "
            Dim strKTTenTacGia As String = " 1=1 "
            Dim strKTNhaXuatBan As String = " 1=1 "
            Dim sKTTheLoaiSach As String = " 1=1 "
            Dim sKTNamXuatBan As String = " 1=1 "
            Dim sKTTinhTrangSach As String = " 1=1 "

            Dim cm As SqlCommand


            If (sachcrt.CheckTenSach = True) Then
                sKTTenDauSach = "ds.Ten like N'%" + sachcrt.Ten + "%' "
            End If


            If (sachcrt.CheckTenTacGia = True) Then
                strKTTenTacGia = "ds.TenTacGia like N'%" + sachcrt.TenTacGia + "%' "
            End If

            If (sachcrt.CheckNhaXuatBan = True) Then
                strKTNhaXuatBan = "ds.NhaXuatBan like N'%" + sachcrt.NhaXuatBan + "%' "
            End If

            If (sachcrt.CheckNamXuatBan = True) Then
                sKTNamXuatBan = "datepart(Year,ds.NamXuatBan) between " + sachcrt.NamXuatBanTu.Year.ToString() + " and " + sachcrt.NamXuatBanDen.Year.ToString() + " "
            End If


            If (sachcrt.CheckTheLoaiSach = True) Then
                sKTTheLoaiSach = "tl.Ten like N'%" + sachcrt.TenTheLoai + "%' "
            End If

            If (sachcrt.CheckTinhTrangSach = True) Then
                sKTTinhTrangSach = "tts.Ten like N'%" + sachcrt.TenTinhTrang + "%'"
            End If


            Dim sql = String.Format("select s.Ma, s.MaDauSach, ds.Ten,ds.TenTacGia,s.MaTinhTrang,ds.MaTheLoai as MaTheLoai,tl.Ten as TenTheLoai, ds.NhaXuatBan, tts.Ten as TenTinhTrangSach, ds.TienPhatMatSach, ds.TriGia from Sach s , DauSach ds, TheLoai tl,TinhTrangSach tts where {0} and s.MaDauSach = ds.Ma and {1} and {2} and  {3} and {4} and ds.MaTheLoai = tl.Ma and {5} and s.MaTinhTrang = tts.Ma", sKTTenDauSach, strKTTenTacGia, strKTNhaXuatBan, sKTNamXuatBan, sKTTheLoaiSach, sKTTinhTrangSach)
            cm = New SqlCommand(sql, cn)

            Dim listsachdto As New List(Of CSachDTO)

            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    listsachdto.Add(New CSachDTO(reader))
                End While
            End Using
            Return listsachdto
        End Using
    End Function

End Class
